package impl;

import dao.StudentDao;
import model.Student;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StudentDaoImpl implements StudentDao {
    static Connection conn;
    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn=DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/aaa?useSSL=false","root", "123456");
        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }
    }
<<<<<<< HEAD
=======
<<<<<<< HEAD

    public void deleteStudent(Student student) {
        String sql = "DELETE FROM student WHERE rollNo = ?";
        try {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, student.getRollNo());
            pstmt.executeUpdate();
            System.out.println("Student: Roll No " + student.getRollNo()
                    +", deleted from database");
=======
>>>>>>> eccb933 (add spring beans and test)
//   增
    public void addStudent(Student student) {
//        编写sql语句
        String sql = "INSERT INTO student (rollNo, name) VALUES (?,?)";
        try {
//         预编译
            PreparedStatement pstmt = conn.prepareStatement(sql);
//          填充占位符
            pstmt.setInt(1, student.getRollNo());
            pstmt.setString(2, student.getName());
//          executeUpdate 的返回值是一个整数，指示受影响的行数（即更新计数）
            pstmt.executeUpdate();
<<<<<<< HEAD
=======
>>>>>>> 024f0df (数据库增删改查的实现)
>>>>>>> eccb933 (add spring beans and test)
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
<<<<<<< HEAD
=======
<<<<<<< HEAD

=======
>>>>>>> eccb933 (add spring beans and test)
    public void deleteStudent(Student student) {
        String sql = "DELETE FROM student WHERE name = ?";
        try {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, student.getName());
<<<<<<< HEAD
            int i=pstmt.executeUpdate();
            if(i>0)
=======
            //函数返回学生name是否存在，不存在返回0
            if(pstmt.executeUpdate()>0)
>>>>>>> eccb933 (add spring beans and test)
                System.out.println("Student: RollNo " + student.getRollNo()
                        +", deleted from database");
            else
            {
                System.out.println("no exist");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
<<<<<<< HEAD

=======
//    查找所有学生信息
>>>>>>> 024f0df (数据库增删改查的实现)
>>>>>>> eccb933 (add spring beans and test)
    public List<Student> getAllStudents() {
        List<Student> students = new ArrayList<>();
        String sql = "SELECT * FROM student";
        try {
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                int rollNo = rs.getInt("rollNo");
                String name = rs.getString("name");
                Student student = new Student(name, rollNo);
                students.add(student);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return students;
    }
<<<<<<< HEAD
=======
<<<<<<< HEAD
>>>>>>> eccb933 (add spring beans and test)

    public Student getStudent(int rollNo) {
        String sql = "SELECT * FROM student WHERE rollNo = ?";
        try {
<<<<<<< HEAD

=======
=======
    // 查找单个学生的信息
    public Student getStudent(int rollNo) {
        String sql = "SELECT * FROM student WHERE rollNo = ?";
        try {

>>>>>>> 024f0df (数据库增删改查的实现)
>>>>>>> eccb933 (add spring beans and test)
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, rollNo);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                String name = rs.getString("name");
                Student student = new Student(name, rollNo);
                return student;
            }
<<<<<<< HEAD

=======
<<<<<<< HEAD
=======

>>>>>>> 024f0df (数据库增删改查的实现)
>>>>>>> eccb933 (add spring beans and test)
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return null;
    }

    public void updateStudent(Student student) {
        String sql = "UPDATE student SET name = ? WHERE rollNo = ?";
        try {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, student.getName());
            pstmt.setInt(2, student.getRollNo());
            pstmt.executeUpdate();
<<<<<<< HEAD
            System.out.println("Student: RollNo " + student.getRollNo()
=======
<<<<<<< HEAD
            System.out.println("Student: Roll No " + student.getRollNo()
=======
            System.out.println("Student: RollNo " + student.getRollNo()
>>>>>>> 024f0df (数据库增删改查的实现)
>>>>>>> eccb933 (add spring beans and test)
                    +", updated in the database");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

<<<<<<< HEAD

=======
<<<<<<< HEAD
    public void addStudent(Student student) {
        String sql = "INSERT INTO student (rollNo, name) VALUES (?,?)";
        try {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, student.getRollNo());
            pstmt.setString(2, student.getName());
            pstmt.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
=======

>>>>>>> 024f0df (数据库增删改查的实现)
>>>>>>> eccb933 (add spring beans and test)
}